/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cz.muni.fi.pa036is.daoimplementation;

import cz.muni.fi.pa036is.daointerface.MatchDAO;
import cz.muni.fi.pa036is.daointerface.RoundDAO;
import cz.muni.fi.pa036is.entity.Match;
import cz.muni.fi.pa036is.entity.Round;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.springframework.stereotype.Repository;

/**
 *
 * @author Kuba
 */
@Repository
public class MatchDAOImpl implements MatchDAO {

    private Connection con = null;
    private Statement st = null;
    private ResultSet rs = null;

    public MatchDAOImpl() throws ClassNotFoundException {



        String url = "jdbc:postgresql://localhost/PA036";
        String user = "postgres";
        String password = "test";

        try {
												Class.forName("org.postgresql.Driver");
            con = DriverManager.getConnection(url, user, password);
        } catch (SQLException ex) {
            Logger.getLogger(MatchDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public void addMatch(Match match) {
        try {

            String insert = "INSERT INTO match"
                    + "(player1_id, player2_id, season_id, date) VALUES"
                    + "(?,?,?,?)";
            PreparedStatement preparedStatement = con.prepareStatement(insert, Statement.RETURN_GENERATED_KEYS);

            preparedStatement.setLong(1, match.getPlayer1Id());
            preparedStatement.setLong(2, match.getPlayer2Id());
            preparedStatement.setLong(3, match.getSeasonId());
            preparedStatement.setTimestamp(4, match.getDateAndTime());

            preparedStatement.executeUpdate();
												
												ResultSet rs = preparedStatement.getGeneratedKeys();
												rs.next();
												Long matchId = rs.getLong(1);
												match.setId(matchId);

        } catch (SQLException ex) {
            Logger.getLogger(MatchDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public void editMatch(Match match) {
        try {

            String update = "UPDATE match SET player1_id= ?, player2_id= ?, season_id= ?, date= ? WHERE id= ?";

            PreparedStatement preparedStatement = con.prepareStatement(update);

            preparedStatement.setLong(1, match.getPlayer1Id());
            preparedStatement.setLong(2, match.getPlayer2Id());
            preparedStatement.setLong(3, match.getSeasonId());
            preparedStatement.setTimestamp(4, match.getDateAndTime());
            preparedStatement.setLong(5, match.getId());

            preparedStatement.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(MatchDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public void deleteMatch(Long id) {
        try {

            String delete = "DELETE FROM match WHERE id=?";

            PreparedStatement preparedStatement = con.prepareStatement(delete);

            preparedStatement.setLong(1, id);

            preparedStatement.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(MatchDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public Match findMatch(Long id) {
        Match match = new Match();
        try {

            String find = "SELECT * FROM match WHERE id=?";

            PreparedStatement preparedStatement = con.prepareStatement(find);
												
												preparedStatement.setLong(1, id);

            rs = preparedStatement.executeQuery();

            rs.next();
            match.setId(rs.getLong(1));
            match.setPlayer1Id(rs.getLong(2));
            match.setPlayer2Id(rs.getLong(3));
            match.setSeasonId(rs.getLong(4));
            match.setDateAndTime(rs.getTimestamp(5));

        } catch (SQLException ex) {
            Logger.getLogger(MatchDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return match;
    }

    @Override
    public List<Match> getAllMatchesForPerson(Long id) {
        List<Match> matchList = new ArrayList<Match>();

        try {

            String getAll = "SELECT * FROM match WHERE player1_id = ? OR player2_id = ?";


            PreparedStatement preparedStatement = con.prepareStatement(getAll);

            preparedStatement.setLong(1, id);
            preparedStatement.setLong(2, id);

            rs = preparedStatement.executeQuery();

            while (rs.next()) {
                Match match = new Match();
                match.setId(rs.getLong(1));
                match.setPlayer1Id(rs.getLong(2));
                match.setPlayer2Id(rs.getLong(3));
                match.setSeasonId(rs.getLong(4));
                match.setDateAndTime(rs.getTimestamp(5));
                matchList.add(match);

            }
        } catch (SQLException ex) {
            Logger.getLogger(MatchDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return matchList;
    }

    @Override
    public List<Match> getAllMatchesForSeason(Long id) {
        List<Match> matchList = new ArrayList<Match>();

        try {

            String getAll = "SELECT * FROM match WHERE season_id = ?";


            PreparedStatement preparedStatement = con.prepareStatement(getAll);

            preparedStatement.setLong(1, id);

            rs = preparedStatement.executeQuery();

            while (rs.next()) {
                Match match = new Match();
                match.setId(rs.getLong(1));
                match.setPlayer1Id(rs.getLong(2));
                match.setPlayer2Id(rs.getLong(3));
                match.setSeasonId(rs.getLong(4));
                match.setDateAndTime(rs.getTimestamp(5));
                matchList.add(match);

            }
        } catch (SQLException ex) {
            Logger.getLogger(MatchDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return matchList;
    }
				
    @Override
    public List<Match> getAllMatches() {
        List<Match> matchList = new ArrayList<Match>();

        try {

            String getAll = "SELECT * FROM match";


            PreparedStatement preparedStatement = con.prepareStatement(getAll);

            rs = preparedStatement.executeQuery();

            while (rs.next()) {
                Match match = new Match();
                match.setId(rs.getLong(1));
                match.setPlayer1Id(rs.getLong(2));
                match.setPlayer2Id(rs.getLong(3));
                match.setSeasonId(rs.getLong(4));
                match.setDateAndTime(rs.getTimestamp(5));
                matchList.add(match);

            }
        } catch (SQLException ex) {
            Logger.getLogger(MatchDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return matchList;

    }

	@Override
	public List<Match> getMatchesByPersonAndSeason(Long personId, Long seasonId) {
		List<Match> matchList = new ArrayList<Match>();

        try {

            String getAll = "SELECT * FROM match WHERE (player1_id = ? OR player2_id = ?) AND season_id = ?";


            PreparedStatement preparedStatement = con.prepareStatement(getAll);

            preparedStatement.setLong(1, personId);
            preparedStatement.setLong(2, personId);
			preparedStatement.setLong(3, seasonId);

            rs = preparedStatement.executeQuery();

            while (rs.next()) {
                Match match = new Match();
                match.setId(rs.getLong(1));
                match.setPlayer1Id(rs.getLong(2));
                match.setPlayer2Id(rs.getLong(3));
                match.setSeasonId(rs.getLong(4));
                match.setDateAndTime(rs.getTimestamp(5));
                matchList.add(match);

            }
        } catch (SQLException ex) {
            Logger.getLogger(MatchDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return matchList;
	}

	@Override
	public int getTotalPointsForPlayer1(Match m) {
		try {
			RoundDAO roundDAO = new RoundDAOImpl();
			int totalPoints = 0;
			for (Round r: roundDAO.getAllRoundsForMatch(m.getId())) {
				totalPoints += r.getPointCount1();
			}
			
			return totalPoints;
			
		} catch (ClassNotFoundException ex) {
			Logger.getLogger(MatchDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
		}
		
		return 0;
	}

	@Override
	public int getTotalPointsForPlayer2(Match m) {
		try {
			RoundDAO roundDAO = new RoundDAOImpl();
			int totalPoints = 0;
			for (Round r: roundDAO.getAllRoundsForMatch(m.getId())) {
				totalPoints += r.getPointCount2();
			}
			
			return totalPoints;
			
		} catch (ClassNotFoundException ex) {
			Logger.getLogger(MatchDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
		}
		
		return 0;
	}
}
